package com.util;

import com.Bean.*;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class JdbcUtil {

    static {
        //加载驱动,驱动只需加载一次
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private  static  String url="jdbc:mysql://120.24.170.171:3306/hotel?useUnicode=true&characterEncoding=UTF-8&serverTimezone=PRC";
    private  static  String username="root";
    private  static  String password="Tangwenjie@0210";

    public static Connection getConnection() throws SQLException {
        Connection connection =  DriverManager.getConnection(url,username,password);
        return  connection;
    }

    /**
     *
     * @param sql 传入insert,update或delete语句
     * @param values   sql语句?号占位符的值列表
     *        如参数   sql = delete from emp where empno=?   则   vaues=new Object[]{7788}
     *        如参数   sql = insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)
     *         则  values = new Object[]{6666,"哈哈","SALES",7788,"2022-01-01",999999.99,10000,10}
     *        如参数为: sql = delete from emp where deptno=10  则  values=null
     * @return
     */
    public static int insertOrUpdateOrDelete(String sql ,Object[] values) throws SQLException {

        Connection connection=null;//声明
        try {
            ThreadLocal<Connection> connectionThreadLocal =   SingletonThreadLocal.getInstance().getThreadLocal();//得到一个ThreadLocal对象
            ///connection = getConnection();
            connection = connectionThreadLocal.get();//获得当前线程中由ThreadLocal对象中存储 的那个connection对象

            //往订单表中写入数据  关闭连接

            //往订单详情表中写入数据  连接已被关闭


            
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    //循环取出values中的数据传入到?号占位符中
                    preparedStatement.setObject(i + 1, values[i]);//setXX方法的下标是1开始，数组的下标是从0开始
                }
            }

            int res = preparedStatement.executeUpdate();
            return  res;
        }finally {
            if (connection!=null) {
            //  connection.close();
            }

        }




    }

    /**
     *
     * @param sql
     * @param values
     * @return
     *    返回查询结果，查询结果 的第一行数据存储到一个HashMap对象中，多条数据(多个HashMap) 存储到List集合中做方法返回值
     */
    public static List<HashMap<String,Object>> queryData(String sql, Object[] values) throws SQLException {
        Connection  connection = null;

        try{

            connection = getConnection();

            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            if (values!=null){
                for(int i=0;i<values.length;i++){
                    preparedStatement.setObject(i+1,values[i]);
                }
            }

            ResultSet rs =   preparedStatement.executeQuery();//得到查询结果
            //得到查询结果的元数据
            ResultSetMetaData metaData =   rs.getMetaData();

            int columnCount = metaData.getColumnCount();//得到查询结果的列数

            List<HashMap<String,Object>>   tableRowDataMap = new ArrayList<>();

            while(rs.next()){//循环遍历 结果集中的每一条数据
                    HashMap<String,Object> rowData = new HashMap<>();
                    for(int i=1;i<=columnCount;i++){//取出一行中的每一列数据
                        String columnName =  metaData.getColumnLabel(i).toLowerCase();
                        String columnTypeName = metaData.getColumnTypeName(i);//得到列的数据类型 (INT,NUMERIC,DATE,VARCHAR)
                        //根据列名称取出该列对应的值,从数据库中取出的列值在java中到底是什么类型我们是未知的，通常字符型(varchar,char)类型都是String类型接收的
                        //数值型(int,Numeric等)在java中是用BigDecimal表示的
                        //日期类型(Date) 在java中是用 java.util.Date表示
                        //最好是根据数据库的数据类型，调用相应的getXXX方法取得具体类型

                        Object columnValue =  null;
                        if (columnTypeName.equalsIgnoreCase("INT")){
                            columnValue = rs.getInt(columnName);
                        }else  if (columnTypeName.equalsIgnoreCase("NUMERIC")){
                            columnValue = rs.getDouble(columnName);
                        }else  if (columnTypeName.equalsIgnoreCase("DATE")|| columnTypeName.equalsIgnoreCase("DATETIME")){
                            columnValue = rs.getString(columnName);
                        }else  if (columnTypeName.equalsIgnoreCase("VARCHAR")){
                            columnValue = rs.getString(columnName);
                        }else{
                            columnValue = rs.getObject(columnName);
                        }




                        rowData.put(columnName,columnValue);//把列名，列值放到HashMap对象中
                    }
                tableRowDataMap.add(rowData);//把每一个HashMap对象放到List集合中
            }

            return  tableRowDataMap;//返回List集合，list集合存储了查询的结果


        }finally {
            if (connection!=null){
                connection.close();
            }
        }




    }

    /**
     *
     * @param sql   传入select语句
     * @param values  传入 select语句的?号占位符的条件
     *      如: sql = "select * from emp where deptno=?"  则values=new Object[]{部门号}
     *         sql = "select * from emp where deptno=? and sal>? "  则values=new Object[]{部门号,工资}
     *
     *     代码封装的思想把变化的部分通过方法的参数传递进来
     *           取数据的操作肯定是每一个SQL语句都有自己取数据的一段代码,可以利用java的多态机制回调相应的代码
     *
     * @return
     *    返回 List<Object>集合，返回结果中存储了sql语句的查询结果。
     *
     * 应该可以将查询封装成一个公共方法，方法的参数为要执行SQL查询，还有一个查询语句的?号占位符的条件
     */
    public static <T> List<T> queryData(String sql , Object[] values, RowMapper<T> rowMapper) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if (values!=null){
                for(int i=0;i<values.length;i++){
                    preparedStatement.setObject(i+1,values[i]);
                }
            }
            ResultSet resultSet =  preparedStatement.executeQuery();

            List<T> rowDataList = new ArrayList<>();
            while(resultSet.next()){
             //获得表中的一条记录和JavaBean的映射结果
               T rowData =    rowMapper.getRowData(resultSet);//调用rowMapper接口中的方法，回调
                rowDataList.add(rowData);//把查询结果存储到list集合中
            }
            //返回查询结果
            return  rowDataList;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }
    public static <T> PageUtil<T> queryDataByPage(String sql,Class<T> cls, int currentPage,int pageSize, Object... values) throws SQLException, InstantiationException, IllegalAccessException {
//        /**
//         * 每页显示的记录条数
//         */
//        private  int pageSize;
//        /**
//         * 总页数
//         */
//        private  int totalPage;
//        /**
//         * 总记录数
//         */
//        private  int totalRecord;
//        /**
//         * 当前的页码
//         */
//        private  int currentPage;
//
//        /**
//         * 查询结果保存在此属性中
//         */
//        private  T data;

        String sql1="select count(*) sum from("+sql+") t";
        List<HashMap<String, Object>> hashMaps = JdbcUtil.queryData(sql1, values);
        Object sum = hashMaps.get(0).get("sum");
        int totalRecord= Integer.parseInt(String.valueOf(sum));


        String sql2=sql+" limit ?,?";

        Object[] newArray=  new Object[values.length+2];
        System.arraycopy(values,0,newArray,0,values.length);

        newArray[newArray.length-2]=(currentPage-1)*pageSize;
        newArray[newArray.length-1]=pageSize;
        List<T> ts = JdbcUtil.queryData(sql2, cls, newArray);

//         public PageUtil(int pageSize, int totalRecord, int currentPage, T data) {
        PageUtil<T> listPageUtil = new PageUtil(pageSize, totalRecord, currentPage, ts);
        return listPageUtil;
    }
        /**
         *
         * 根据sql语句，结合第二个参数class对应的javaBean把查询结果映射到指定的JavaBean
         *   //目前可以处理单表查询操作
         * @param sql 传入 sql语句
         * @param cls 传入JavaBean对应的class
         * @param values  sql语句对应的?号占位符的条件值
         * @param <T>
         * @return
         * @throws SQLException
         */
    public static <T> List<T> queryData(String sql,Class<T> cls, Object... values) throws SQLException, InstantiationException, IllegalAccessException {
        Connection  connection = null;

        try{

            connection = getConnection();

            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            if (values!=null){
                for(int i=0;i<values.length;i++){
                    preparedStatement.setObject(i+1,values[i]);
                }
            }

            ResultSet rs =   preparedStatement.executeQuery();//得到查询结果
            //得到查询结果的元数据
            ResultSetMetaData metaData =   rs.getMetaData();

            int columnCount = metaData.getColumnCount();//得到查询结果的列数

            List<T>   tableRowDataMap = new ArrayList<>();//做为方法返回值的集合对象

            while(rs.next()){//循环遍历 结果集中的每一条数据
                HashMap<String,Object> rowData = new HashMap<>();
                for(int i=1;i<=columnCount;i++){//取出一行中的每一列数据
                    String columnName =  metaData.getColumnLabel(i).toLowerCase();
                    String columnTypeName = metaData.getColumnTypeName(i);//得到列的数据类型 (INT,NUMERIC,DATE,VARCHAR)

                    Object columnValue =  null;
                    if (columnTypeName.equalsIgnoreCase("INT")){
                        columnValue = rs.getInt(columnName);
                    }else  if (columnTypeName.equalsIgnoreCase("NUMERIC")){
                        columnValue = rs.getDouble(columnName);
                    }else  if (columnTypeName.equalsIgnoreCase("DATE")|| columnTypeName.equalsIgnoreCase("DATETIME")){
                        columnValue = rs.getString(columnName);
                    }else  if (columnTypeName.equalsIgnoreCase("VARCHAR")){
                        columnValue = rs.getString(columnName);
                    }else{
                        columnValue = rs.getObject(columnName);
                    }
                    rowData.put(columnName,columnValue);//把列名，列值放到HashMap对象中
                }


               T t =      ReflectUtil.mapValueToBean(rowData,cls);//反射实现代码的封装。
                tableRowDataMap.add(t);

            }

            return  tableRowDataMap;//返回List集合，list集合存储了查询的结果


        }finally {
            if (connection!=null){
                connection.close();
            }
        }




    }

    //根据房间类型名称，返回所有房间list
    public static List<RoomId> queryroomidweretypename(String sql , Object[] values) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if (values!=null){
                for(int i=0;i<values.length;i++){
                    preparedStatement.setObject(i+1,values[i]);
                }
            }
            ResultSet resultSet =  preparedStatement.executeQuery();

            List<RoomId> rowDataList = new ArrayList<>();
            while(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                String typeid = resultSet.getString(1);
                String roomid = resultSet.getString(2);
                String roomtype = resultSet.getString(3);
                int typename = resultSet.getInt(4);
                RoomId roomId = new RoomId();
                roomId.setRid(roomid);
                roomId.setRstatus(roomtype);
                roomId.setTimetype(typename);
                roomId.setRtid(typeid);
                rowDataList.add(roomId);//把查询结果存储到list集合中
            }
            //返回查询结果
            return  rowDataList;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

    //返回所有房间类型和房间list
    public static List<RoomAndRoomType> queryroomidandtype(String sql ) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            System.out.println("sql:"+sql);
            ResultSet resultSet =  preparedStatement.executeQuery();

            List<RoomAndRoomType> rowDataList = new ArrayList<>();
            while(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                String typeid = resultSet.getString(1);
                String roomid = resultSet.getString(2);
                String roomtype = resultSet.getString(3);
                int typename = resultSet.getInt(4);

                String a = resultSet.getString(5);
                double b = resultSet.getDouble(6);
                double c = resultSet.getDouble(7);
                double d = resultSet.getDouble(8);
                RoomAndRoomType roomId = new RoomAndRoomType();
                roomId.setRid(roomid);
                roomId.setRstatus(roomtype);
                roomId.setTimetype(typename);
                roomId.setRtid(typeid);

                roomId.setRname(a);
                roomId.setWkprice(b);
                roomId.setWeprice(c);
                roomId.setRnumber(d);

                rowDataList.add(roomId);//把查询结果存储到list集合中
            }
            //返回查询结果
            return  rowDataList;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

    //返回所有商品类型和房间list
    public static List<GoodsAndType> queryGoodsdandtype(String sql ) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            ResultSet resultSet =  preparedStatement.executeQuery();

            List<GoodsAndType> rowDataList = new ArrayList<>();
            while(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                String gtid = resultSet.getString(1);
                String gname = resultSet.getString(2);
                String gprice = resultSet.getString(3);
                String gid = resultSet.getString(4);
                String gtype = resultSet.getString(5);

                Goods goods = new Goods();
                goods.setGid(gid);
                goods.setGname(gname);
                goods.setGprice(gprice);
                goods.setGtid(gtid);
                GoodsAndType goodsAndType = new GoodsAndType();
                goodsAndType.setGoods(goods);
                goodsAndType.setGtype(gtype);
                rowDataList.add(goodsAndType);//把查询结果存储到list集合中
            }
            //返回查询结果
            return  rowDataList;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

    //返回所有房间类型list
    public static List<Room> queryroomtype(String sql) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            ResultSet resultSet =  preparedStatement.executeQuery();

            List<Room> rowDataList = new ArrayList<>();
            while(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                String rtid = resultSet.getString(1);
                String rname = resultSet.getString(2);
                Double wkprice = resultSet.getDouble(3);
                Double weprice = resultSet.getDouble(4);
                Double rnumb = resultSet.getDouble(5);
                Room room = new Room();
                room.setRtid(rtid);
                room.setRname(rname);
                room.setWkprice(wkprice);
                room.setWeprice(weprice);
                room.setRnumber(rnumb);
                rowDataList.add(room);//把查询结果存储到list集合中
            }
            //返回查询结果
            return  rowDataList;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }


    //返回所有房间类型list
    public static List<PaymentTerm> querypaytype(String sql) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            ResultSet resultSet =  preparedStatement.executeQuery();

            List<PaymentTerm> rowDataList = new ArrayList<>();
            while(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                String id = resultSet.getString(1);
                String name = resultSet.getString(2);

                PaymentTerm pay = new PaymentTerm();
                pay.setSid(id);
                pay.setSname(name);
                rowDataList.add(pay);//把查询结果存储到list集合中
            }
            //返回查询结果
            return  rowDataList;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

//返回uid
public static String queryuId(String sql,String name) throws SQLException {
    Connection  connection = null;
    try{
        connection = getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1,name);

        ResultSet resultSet =  preparedStatement.executeQuery();

        if(resultSet.next()){
            //获得表中的一条记录和JavaBean的映射结果
            return resultSet.getString(1);
        }
        //返回查询结果
        return  null;

    }finally {
        if (connection!=null){
            connection.close();
        }
    }


}
    //返回Lid
    public static String querylId(String sql,String rid) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setObject(1,rid);
            preparedStatement.setObject(2,rid);
            ResultSet resultSet =  preparedStatement.executeQuery();

            if(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                return resultSet.getString(1);
            }
            //返回查询结果
            return  null;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

    //返回Lid
    public static Double queryllcash(String sql,String rid) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setObject(1,rid);
            ResultSet resultSet =  preparedStatement.executeQuery();

            if(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                return resultSet.getDouble(1);
            }
            //返回查询结果
            return  null;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

    //根据Lid返回totalprice
    public static RoomList queryTotalPricewherelId(String sql,String lid) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setObject(1,lid);
            ResultSet resultSet =  preparedStatement.executeQuery();

            if(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                RoomList roomList = new RoomList();
                roomList.setLroomprice(resultSet.getDouble(1));;
                roomList.setLotherprice(resultSet.getDouble(2));;
                 roomList.setLcash(resultSet.getDouble(3));;
                 roomList.setLsum(resultSet.getDouble(4));;
                 return roomList;
            }
            //返回查询结果
            return  null;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }

    //返回所有房间类型list
    public static String queryGoodsId(String sql,String goods) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setObject(1,goods);
            ResultSet resultSet =  preparedStatement.executeQuery();

            List<Room> rowDataList = new ArrayList<>();
            if(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                return resultSet.getString(1);
            }
            //返回查询结果
            return  null;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }
    //返回所有房间总数
    public static Double queryRooms(String sql) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);


            ResultSet resultSet =  preparedStatement.executeQuery();

            List<Room> rowDataList = new ArrayList<>();
            if(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                return resultSet.getDouble(1);
            }
            //返回查询结果
            return  null;

        }finally {
            if (connection!=null){
                connection.close();
            }
        }


    }
    public static Client SelectIdCard(String sql,String idcard) throws SQLException {
        Connection  connection = null;
        try{
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setObject(1,idcard);
            ResultSet resultSet =  preparedStatement.executeQuery();

            List<Room> rowDataList = new ArrayList<>();
            if(resultSet.next()){
                //获得表中的一条记录和JavaBean的映射结果
                Client client = new Client();
                client.setPno(resultSet.getString(1));
                client.setCliPid(resultSet.getString(2));
                client.setPname(resultSet.getString(3));
                client.setPphone(resultSet.getString(5));
                client.setPid(resultSet.getString(4));
                client.setPtotal(resultSet.getDouble(6));
                client.setPcount(resultSet.getInt(7));
                client.setLasttime(resultSet.getDate(8));
                client.setPcretime(resultSet.getString(9));
                client.setPbrithday(resultSet.getString(10));
                client.setPgender(resultSet.getString(11));

                return client;
            }
            //返回查询结果
            return  null;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (connection!=null){
                connection.close();
            }
        }
return null;
    }

}
